﻿Imports MySql.Data.MySqlClient
Public Class txt_totalCost
    Public SQL As MySqlConnection
    Public newcourseid As String
    Public newname3_2_1 As String
    Public newcode3_2_1 As String
    Public checkedApp As Integer
    Public Path_Picture As String
    Dim trainingHour As Integer
    Dim respone As Object

    Dim course_name As String
    Dim trainer As String
    Dim date_train As String
    Dim duration As String
    Dim course_cost As String
    Dim employee_name As String
    Dim employee_code As String

    Dim training_approve As String
    Dim training_type As String

    Public time_start As String
    Public time_end As String
    Dim total_cost_on As Integer = 0
    Dim total_cost_group As Integer = 0
    Dim total_hours_on As Date
    Dim total_hours_group As Date
    Dim start_date As String



    Public Sub New(ByRef SQLConnection As MySqlConnection, ByVal names As String, ByVal code As String, ByVal courseid As String, ByVal coursename As String)
        InitializeComponent()
        SQL = SQLConnection

        txt_empCode.Text = code
        txt_empName.Text = names
        newcode3_2_1 = code
        newname3_2_1 = names

    End Sub

    Private Sub personal3_2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim sel_employ_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim sel_employ_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim sel_employ_mysqlAdapter As New MySqlCommand


        Dim sel_dept_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim sel_dept_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim sel_dept_mysqlAdapter As New MySqlCommand


        Dim selectDept As String
        Dim selectEmployee As String
        Dim dept_id As String
        txt_thai_id.Text = ""
        selectEmployee = "Select * from employee where emp_code = '" & newcode3_2_1 & "'"


        Dim mysqlCon As MySqlConnection
        Dim mysqlCon1 As MySqlConnection

        mysqlCon1 = New MySqlConnection
        mysqlCon = New MySqlConnection

        mysqlCon.ConnectionString = "server=127.0.0.1;port = 3306;user id=" + "root" + ";password=" + "software" + ";database=testremote;Character Set =utf8"
        mysqlCon1.ConnectionString = "server=127.0.0.1;port = 3306;user id=" + "root" + ";password=" + "software" + ";database=testremote;Character Set =utf8"


        Try

            mysqlCon.Open()
            mysqlCon1.Open()

            sel_employ_mysqlAdapter.CommandText = selectEmployee
            sel_employ_mysqlAdapter.Connection = mysqlCon
            sel_employ_mysqlda.SelectCommand = sel_employ_mysqlAdapter
            sel_employ_mysqldr = sel_employ_mysqlAdapter.ExecuteReader

            While sel_employ_mysqldr.Read()
                txt_position.Text = sel_employ_mysqldr("emp_position")
                txt_level.Text = sel_employ_mysqldr("emp_level")
                dept_id = sel_employ_mysqldr("dept_id")

                Try
                    txt_thai_id.Text = sel_employ_mysqldr("emp_thai_id")
                Catch
                    txt_thai_id.Text = " "
                End Try
                txt_startdate.Text = sel_employ_mysqldr("emp_start_date")

                selectDept = "Select * from department where dept_id = '" & dept_id & "'"

                sel_dept_mysqlAdapter.CommandText = selectDept
                sel_dept_mysqlAdapter.Connection = mysqlCon1
                sel_dept_mysqlda.SelectCommand = sel_dept_mysqlAdapter
                sel_dept_mysqldr = sel_dept_mysqlAdapter.ExecuteReader
                While sel_dept_mysqldr.Read()

                    txt_company.Text = sel_dept_mysqldr("comp_name")
                    txt_department.Text = sel_dept_mysqldr("dept_name")
                    txt_division.Text = sel_dept_mysqldr("dev_name")

                End While
            End While
        Catch ex As Exception
            MsgBox("Can't Connect to database")
        End Try

        Dim split_start() As String
        split_start = Split(txt_startdate.Text, "/")
        Dim start_year As Integer
        Dim start_month As Integer
        Dim now_year As Integer
        Dim now_month As Integer
        Dim total As Integer

        start_year = CInt(split_start(2))

        start_month = CInt(split_start(1))
        now_year = CInt(Date.Now.Year.ToString)
        now_month = CInt(Date.Now.Month.ToString)

        If start_year > 2100 Then
            total = 12 * (now_year - start_year + 543) + now_month

            txt_month.Text = (total - start_month) Mod 12
            txt_year.Text = ((total - start_month) - ((total - start_month) Mod 12)) / 12


        Else
            total = 12 * (now_year - start_year) + now_month

            txt_month.Text = (total - start_month) Mod 12
            txt_year.Text = ((total - start_month) - ((total - start_month) Mod 12)) / 12

        End If



        show_listView()
        call_sum_cost()

        mysqlCon.Close()
        mysqlCon1.Close()


    End Sub
    Public Sub show_listView()
        ListView1.Items.Clear()
        ListView2.Items.Clear()


        Dim selectShowListViewOn As String
        Dim selectShowListViewGroup As String
        Dim selectCourse As String
        Dim trainingCost As String

        Dim mysqlCon As MySqlConnection
        Dim mysqlCon1 As MySqlConnection
        Dim mysqlCon2 As MySqlConnection
        Dim mysqlCon3 As MySqlConnection

        mysqlCon = New MySqlConnection
        mysqlCon1 = New MySqlConnection
        mysqlCon2 = New MySqlConnection
        mysqlCon3 = New MySqlConnection


        Dim sel_group_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim sel_group_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim sel_group_mysqlAdapter As New MySqlCommand


        Dim sel_On_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim sel_On_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim sel_On_mysqlAdapter As New MySqlCommand


        Dim sel_course_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim sel_course_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim sel_course_mysqlAdapter As New MySqlCommand


        selectShowListViewOn = "Select * from training where emp_code = '" & txt_empCode.Text & "' and training_type = 'On the job' and training_date >= '" & DateTimeStart.Value.Year & "-" & DateTimeStart.Value.Month & "-" & DateTimeStart.Value.Day & "' and training_date <= '" & DateTimeEnd.Value.Year & "-" & DateTimeEnd.Value.Month & "-" & DateTimeEnd.Value.Day & "'"
        selectShowListViewGroup = "Select * from training where emp_code = '" & txt_empCode.Text & "' and training_type = 'Training Group'and training_date >= '" & DateTimeStart.Value.Year & "-" & DateTimeStart.Value.Month & "-" & DateTimeStart.Value.Day & "' and training_date <= '" & DateTimeEnd.Value.Year & "-" & DateTimeEnd.Value.Month & "-" & DateTimeEnd.Value.Day & "'"


        mysqlCon.ConnectionString = "server=127.0.0.1;port = 3306;user id=" + "root" + ";password=" + "software" + ";database=testremote;Character Set =utf8"
        mysqlCon1.ConnectionString = "server=127.0.0.1;port = 3306;user id=" + "root" + ";password=" + "software" + ";database=testremote;Character Set =utf8"
        mysqlCon2.ConnectionString = "server=127.0.0.1;port = 3306;user id=" + "root" + ";password=" + "software" + ";database=testremote;Character Set =utf8"
        mysqlCon3.ConnectionString = "server=127.0.0.1;port = 3306;user id=" + "root" + ";password=" + "software" + ";database=testremote;Character Set =utf8"
        Dim split_start() As String

        Try

            mysqlCon.Open()
            mysqlCon2.Open()

            sel_On_mysqlAdapter.CommandText = selectShowListViewOn
            sel_On_mysqlAdapter.Connection = mysqlCon
            sel_On_mysqlda.SelectCommand = sel_On_mysqlAdapter
            sel_On_mysqldr = sel_On_mysqlAdapter.ExecuteReader


            sel_group_mysqlAdapter.CommandText = selectShowListViewGroup
            sel_group_mysqlAdapter.Connection = mysqlCon2
            sel_group_mysqlda.SelectCommand = sel_group_mysqlAdapter
            sel_group_mysqldr = sel_group_mysqlAdapter.ExecuteReader

            While sel_group_mysqldr.Read()
                selectCourse = "Select * from course where course_id = '" & sel_group_mysqldr("course_id") & "'"
                mysqlCon3.Open()
                sel_course_mysqlAdapter.CommandText = selectCourse
                sel_course_mysqlAdapter.Connection = mysqlCon3
                sel_course_mysqlda.SelectCommand = sel_course_mysqlAdapter
                sel_course_mysqldr = sel_course_mysqlAdapter.ExecuteReader

                While sel_course_mysqldr.Read()

                    With ListView2.Items.Add(sel_course_mysqldr("course_name"))
                        .subItems.add(sel_course_mysqldr("course_trainer"))
                        split_start = Split(sel_group_mysqldr("training_date"), "/")

                        .subItems.add(split_start(0) + "/" + split_start(1) + "/" + split_start(2))

                        .subItems.add(sel_group_mysqldr("time_start") + "-" + sel_group_mysqldr("time_end"))
                        .subItems.add(sel_group_mysqldr("training_hours"))
                        .subItems.add(sel_group_mysqldr("training_cost_person"))
                    End With

                End While
                sel_course_mysqldr.Close()
                mysqlCon3.Close()

            End While

            While sel_On_mysqldr.Read()
                selectCourse = "Select * from course where course_id = '" & sel_On_mysqldr("course_id") & "'"
                mysqlCon1.Open()
                sel_course_mysqlAdapter.CommandText = selectCourse
                sel_course_mysqlAdapter.Connection = mysqlCon1
                sel_course_mysqlda.SelectCommand = sel_course_mysqlAdapter
                sel_course_mysqldr = sel_course_mysqlAdapter.ExecuteReader

                While sel_course_mysqldr.Read()
                    With ListView1.Items.Add(sel_course_mysqldr("course_name"))
                        .subItems.add(sel_course_mysqldr("course_trainer"))

                        split_start = Split(sel_On_mysqldr("training_date"), "/")

                        .subItems.add(split_start(0) + "/" + split_start(1) + "/" + split_start(2))


                        .subItems.add(sel_On_mysqldr("time_start") + "-" + sel_On_mysqldr("time_end"))
                        .subItems.add(sel_On_mysqldr("training_hours"))
                        .subItems.add(sel_On_mysqldr("training_cost_person"))
                    End With
                End While
                sel_course_mysqldr.Close()
                mysqlCon1.Close()


            End While
            txt_totalCost_on.Text = total_cost_on
        Catch ex As Exception

        End Try



        mysqlCon.Close()

        mysqlCon1.Close()

    End Sub
    Private Sub DateTimeStart_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimeStart.ValueChanged

        show_listView()
        call_sum_cost()

    End Sub
    Public Sub call_sum_cost()
        total_cost_group = 0
        total_cost_on = 0

        Dim total_h_on As Integer = 0
        Dim total_h_Group As Integer = 0
        Dim total_m_on As Integer = 0
        Dim total_m_Group As Integer = 0

        Dim mod_m As Integer
        Dim divide_m_group As Integer
        Dim divide_m_on As Integer
        Dim split_hours() As String
        For J = 0 To ListView1.Items.Count - 1
            'MsgBox(ListView1.Items(J).SubItems(4).Text)
            total_cost_on += CInt(ListView1.Items(J).SubItems(5).Text)
            split_hours = Split(ListView1.Items(J).SubItems(4).Text, ".")
            total_h_Group += CInt(split_hours(0))
            total_m_Group += CInt(split_hours(1))
        Next

        mod_m = total_m_Group \ 60
        total_h_Group += mod_m
        divide_m_group = total_m_Group Mod 60


        '' txt_totalHr_On.Text = total_h & "." & divide_m

        For J = 0 To ListView2.Items.Count - 1
            total_cost_group += CInt(ListView2.Items(J).SubItems(5).Text)
            split_hours = Split(ListView2.Items(J).SubItems(4).Text, ".")
            total_h_on += CInt(split_hours(0))
            total_m_on += CInt(split_hours(1))
        Next


        mod_m = total_m_Group \ 60
        total_h_on += mod_m
        divide_m_on = total_m_on Mod 60

        addZero(total_h_on, divide_m_on, total_h_Group, divide_m_group)

        txt_totalHr_On.Text = time_end
        txt_totalHours_Group.Text = time_start


        txt_totalCost_group.Text = total_cost_group
        txt_totalCost_on.Text = total_cost_on
        txt_sum_onthejob.Text = time_end
        txt_traininggroup.Text = time_start
        Dim spilt_1() As String
        Dim split_2() As String
        Dim int_h_on As Integer
        Dim int_m_on As Integer
        Dim int_h_group As Integer
        Dim int_m_group As Integer

        Dim total_h As Integer
        Dim total_m As Integer
        Dim mod_m1 As Integer
        Dim devide_m As Integer
        spilt_1 = Split(txt_sum_onthejob.Text, ".")
        split_2 = Split(txt_traininggroup.Text, ".")

        int_h_on = CInt(spilt_1(0))
        int_m_on = CInt(spilt_1(1))

        int_h_group = CInt(split_2(0))
        int_m_group = CInt(split_2(1))

        total_h = int_h_on + int_h_group
        total_m = int_m_group + int_m_on

        mod_m1 = total_m \ 60

        total_h += mod_m1
        devide_m = total_m Mod 60
        addZero(total_h.ToString, devide_m.ToString, "0", "0")

        txt_total.Text = time_start.ToString


    End Sub


    Private Sub DateTimeEnd_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimeEnd.ValueChanged
        show_listView()
        call_sum_cost()

    End Sub

    Private Sub ListView1_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseClick

    End Sub

    Private Sub ListView1_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseDoubleClick
        course_name = ListView1.SelectedItems(0).Text
        trainer = ListView1.SelectedItems(0).SubItems(1).Text
        date_train = ListView1.SelectedItems(0).SubItems(3).Text
        duration = ListView1.SelectedItems(0).SubItems(4).Text
        course_cost = ListView1.SelectedItems(0).SubItems(5).Text
        employee_code = txt_empCode.Text
        employee_name = txt_empName.Text
        training_type = "On the job"

        Dim nextForm As personal3_2_1 = New personal3_2_1(SQL, course_name, trainer, date_train, duration, course_cost, employee_name, employee_code, training_type)
        nextForm.Show()
        Me.Close()
    End Sub


    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim NextForm As main = New main()
        NextForm.Show()
        Me.Close()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim AddForm As personal3_1 = New personal3_1(SQL)
        AddForm.Show()
        Me.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim mysqlCon As MySqlConnection

        mysqlCon = New MySqlConnection
        mysqlCon.ConnectionString = "server=192.168.1.13;port = 3308;user id=" + "root" + ";password=" + "pisona" + ";database=testremote;Character Set =utf8"

        Dim insert_em_mysqlAdapter As New MySqlCommand



        If Button1.Text <> "Save" Then

            txt_level.ReadOnly = False
            txt_level.BackColor = Color.White
            txt_level.Enabled = True

            txt_position.ReadOnly = False
            txt_position.BackColor = Color.White
            txt_position.Enabled = True

            txt_empName.ReadOnly = False
            txt_empName.BackColor = Color.White
            txt_empName.Enabled = True

            txt_thai_id.ReadOnly = False
            txt_thai_id.BackColor = Color.White
            txt_thai_id.Enabled = True

            Button1.Text = "Save"


        Else
            mysqlCon.Open()
            Dim name_surname As String = Trim(txt_empName.Text)
            Dim name_test() As String
            Dim name As String
            Dim lastname As String

            name_test = Split(name_surname, " ")

            For J = 0 To name_test.Length - 1
                If J = 0 Then
                    name = name_test(0).ToString
                Else
                    If name_test(J).ToString <> "" Then
                        lastname = name_test(J).ToString
                    End If
                End If
            Next

            insert_em_mysqlAdapter.CommandText = "Update employee SET emp_name = '" & name & "',emp_surname = '" & lastname & "',emp_position = '" & txt_position.Text & "', emp_level = '" & txt_level.Text & "', emp_thai_id = '" & txt_thai_id.Text & "'  where emp_code = '" & txt_empCode.Text & "'"
            insert_em_mysqlAdapter.Connection = mysqlCon
            insert_em_mysqlAdapter.ExecuteNonQuery()

            txt_level.ReadOnly = True
            txt_level.BackColor = Color.GhostWhite
            txt_level.Enabled = False

            txt_position.ReadOnly = True
            txt_position.BackColor = Color.GhostWhite
            txt_position.Enabled = False

            txt_empName.ReadOnly = True
            txt_empName.BackColor = Color.GhostWhite
            txt_empName.Enabled = False

            txt_thai_id.ReadOnly = True
            txt_thai_id.BackColor = Color.GhostWhite
            txt_thai_id.Enabled = False

            Button1.Text = "Edit"

            mysqlCon.Close()


        End If

    End Sub

    Private Sub ListView2_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView2.MouseDoubleClick
        course_name = ListView2.SelectedItems(0).Text
        trainer = ListView2.SelectedItems(0).SubItems(1).Text
        date_train = ListView2.SelectedItems(0).SubItems(3).Text
        duration = ListView2.SelectedItems(0).SubItems(4).Text
        course_cost = ListView2.SelectedItems(0).SubItems(5).Text
        employee_code = txt_empCode.Text
        employee_name = txt_empName.Text
        training_type = "Training Group"

        Dim nextForm As personal3_2_1 = New personal3_2_1(SQL, course_name, trainer, date_train, duration, course_cost, employee_name, employee_code, training_type)
        nextForm.Show()
        Me.Close()
    End Sub


    Private Sub Label3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label3.Click

    End Sub

    Private Sub ListView1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListView1.SelectedIndexChanged

    End Sub

    Private Sub PictureBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBox1.Click
        Dim dialogOpenFiles As OpenFileDialog = New OpenFileDialog()
        dialogOpenFiles.Title = "Open File Dialog"
        dialogOpenFiles.InitialDirectory = "c:\"
        dialogOpenFiles.Filter = "All Picture Files (*.jpg,*.bmp,*.gif,*.png,*.tif,*.eps,*.pic,*.raw,*.bmp,*.jpeg,*.tiff,*.dib)|*.jpg;*.bmp;*.gif;*.png;*.tif;*.eps;*.pic;*.raw;*.bmp;*.jpeg;*.tiff;*.dib;*.ico"
        dialogOpenFiles.FilterIndex = 1
        dialogOpenFiles.RestoreDirectory = True
        If dialogOpenFiles.ShowDialog() = DialogResult.OK Then
            Path_Picture = dialogOpenFiles.FileName
            PictureBox1.Load(Path_Picture)
        End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        Dim count_Row As Integer = 9
        Dim count_Row2 As Integer = 9
        Dim pathExcel As String
        FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
        FolderBrowserDialog1.ShowNewFolderButton = True
        FolderBrowserDialog1.SelectedPath = "C:\"
        FolderBrowserDialog1.ShowDialog()

        pathExcel = FolderBrowserDialog1.SelectedPath

        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add
        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)

        With excelsheets
            .Range("A1:N35").Font.Name = "Angsana New"
            .Range("A1:N100").Font.Bold = True
            .Range("A2:N100").Font.Size = 14
            .Range("A:A").ColumnWidth = 1.5
            .Range("B:B").ColumnWidth = 14
            .Range("C:C").ColumnWidth = 14
            .Range("D:D").ColumnWidth = 8.5
            .Range("E:E").ColumnWidth = 8.5
            .Range("F:F").ColumnWidth = 4
            .Range("G:G").ColumnWidth = 8.5
            .Range("H:H").ColumnWidth = 1.5
            .Range("I:I").ColumnWidth = 14
            .Range("J:J").ColumnWidth = 14
            .Range("K:K").ColumnWidth = 8.5
            .Range("L:L").ColumnWidth = 8.5
            .Range("M:M").ColumnWidth = 4
            .Range("N:N").ColumnWidth = 8.5

            .Range("1:1").RowHeight = 22.5

            With .Range("H1")
                .Font.Bold = True
                .Value = "Training Record Personal"
                .Font.Size = 18
                ''.ColumnWidth = 20
            End With


            With .Range("C2")
                .Value = "Name : " + txt_empName.Text
            End With

            With .Range("I2")
                .Value = "Code : " + txt_empCode.Text
            End With

            With .Range("C3")
                .Value = "Position : " + txt_position.Text
            End With

            With .Range("K3")
                .Value = "Company : " + txt_company.Text
            End With

            With .Range("I3")
                .Value = "Department : " + txt_department.Text
            End With

            With .Range("E3")
                .Value = "Division : " + txt_division.Text
            End With
            With .Range("K2")
                .Value = "Level : " + txt_level.Text
            End With
            With .Range("C4")
                .Value = "Thai ID : " + txt_thai_id.Text
            End With
            With .Range("I4")
                .Value = "Start Date : " + txt_startdate.Text
            End With
            With .Range("K4")
                .Value = "Work Period : " + txt_year.Text + " Years " + txt_month.Text + " Month"
            End With
            With .Range("C5")
                .Value = "Training Start Date : " + DateTimeStart.Value.Date
            End With
            With .Range("I5")
                .Value = "Training End Date : " + DateTimeEnd.Value.Date
            End With


            For J = 7 To 10
                .Range("B7:G7").Borders(J).Weight = 2 ' xlThin
            Next
            For J = 7 To 10
                .Range("B8:D8").Borders(J).Weight = 2 ' xlThin
            Next
            For J = 7 To 10
                .Range("C8:C8").Borders(J).Weight = 2 ' xlThin
            Next
            For J = 7 To 10
                .Range("D8:D8").Borders(J).Weight = 2 ' xlThin
            Next
            For J = 7 To 10
                .Range("E8:E8").Borders(J).Weight = 2 ' xlThin
            Next
            For J = 7 To 10
                .Range("F8:F8").Borders(J).Weight = 2 ' xlThin
            Next
            For J = 7 To 10
                .Range("G8:G8").Borders(J).Weight = 2 ' xlThin
            Next
            For J = 7 To 10
                .Range("I7:N7").Borders(J).Weight = 2 ' xlThin
            Next
            For J = 7 To 10
                .Range("I8:I8").Borders(J).Weight = 2 ' xlThin
            Next
            For J = 7 To 10
                .Range("J8:J8").Borders(J).Weight = 2 ' xlThin
            Next
            For J = 7 To 10
                .Range("K8:K8").Borders(J).Weight = 2 ' xlThin
            Next
            For J = 7 To 10
                .Range("L8:L8").Borders(J).Weight = 2 ' xlThin
            Next

            For J = 7 To 10
                .Range("M8:M8").Borders(J).Weight = 2 ' xlThin
            Next

            For J = 7 To 10
                .Range("N8:N8").Borders(J).Weight = 2 ' xlThin
            Next

            With .Range("D7")
                .Value = "On The Job"
            End With
            With .Range("K7")
                .Value = "Training Group"
            End With

            With .Range("B8")
                .Value = "Course"
            End With
            With .Range("C8")
                .Value = "Trainer"
            End With
            With .Range("D8")
                .Value = "Date"
            End With
            With .Range("E8")
                .Value = "Period"
            End With
            With .Range("F8")
                .Value = "Hrs."
            End With
            With .Range("G8")
                .Value = "Cost"
            End With

            With .Range("I8")
                .Value = "Course"
            End With
            With .Range("J8")
                .Value = "Trainer"
            End With
            With .Range("K8")
                .Value = "Date"
            End With
            With .Range("L8")
                .Value = "Period"
            End With
            With .Range("M8")
                .Value = "Hrs."
            End With
            With .Range("N8")
                .Value = "Cost"
            End With



            If ListView1.Items.Count <> 0 Or ListView2.Items.Count <> 0 Then


                For J = 0 To ListView1.Items.Count - 1

                    With .Range("B" & count_Row)
                        .Value = ListView1.Items(J).SubItems(0).Text
                    End With
                    With .Range("C" & count_Row)
                        .Value = ListView1.Items(J).SubItems(1).Text
                    End With
                    With .Range("D" & count_Row)
                        .Value = ListView1.Items(J).SubItems(2).Text
                    End With
                    With .Range("E" & count_Row)
                        .Value = ListView1.Items(J).SubItems(3).Text
                    End With
                    With .Range("F" & count_Row)
                        .NumberFormat = "00.00"
                        .Value = ListView1.Items(J).SubItems(4).Text
                    End With
                    With .Range("G" & count_Row)
                        .NumberFormat = "#,##0.00"
                        .Value = ListView1.Items(J).SubItems(5).Text
                    End With
                    count_Row += 1

                Next



                For J = 0 To ListView2.Items.Count - 1
                    With .Range("I" & count_Row2)
                        .Value = ListView2.Items(J).SubItems(0).Text
                    End With
                    With .Range("J" & count_Row2)
                        .Value = ListView2.Items(J).SubItems(1).Text
                    End With
                    With .Range("K" & count_Row2)
                        .Value = ListView2.Items(J).SubItems(2).Text
                    End With
                    With .Range("L" & count_Row2)
                        .Value = ListView2.Items(J).SubItems(3).Text
                    End With
                    With .Range("M" & count_Row2)
                        .NumberFormat = "00.00"
                        .Value = ListView2.Items(J).SubItems(4).Text
                    End With
                    With .Range("N" & count_Row2)
                        .NumberFormat = "#,##0.00"
                        .Value = ListView2.Items(J).SubItems(5).Text
                    End With
                    count_Row2 += 1

                Next
            End If

            If count_Row > count_Row2 Then
                For J = 9 To count_Row
                    .Range("B" & J.ToString() & ":B" & J.ToString()).Borders(7).Weight = 2 ' xlThin
                    .Range("B" & J.ToString() & ":B" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("C" & J.ToString() & ":C" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("D" & J.ToString() & ":D" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("E" & J.ToString() & ":E" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("F" & J.ToString() & ":F" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("G" & J.ToString() & ":G" & J.ToString()).Borders(10).Weight = 2 ' xlThin


                    .Range("I" & J.ToString() & ":I" & J.ToString()).Borders(7).Weight = 2 ' xlThin
                    .Range("I" & J.ToString() & ":I" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("J" & J.ToString() & ":J" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("K" & J.ToString() & ":K" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("L" & J.ToString() & ":L" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("M" & J.ToString() & ":M" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("N" & J.ToString() & ":N" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                Next

                count_Row = count_Row + 1
                .Range("B" & count_Row.ToString() & ":G" & count_Row.ToString()).Borders(8).Weight = 2 ' xlThin
                .Range("I" & count_Row.ToString() & ":n" & count_Row.ToString()).Borders(8).Weight = 2 ' xlThin

                count_Row = count_Row + 1


                .Range("E" & count_Row.ToString()).Value = "Total"
                .Range("F" & count_Row.ToString()).NumberFormat = "00.00"
                .Range("F" & count_Row.ToString()).Value = txt_totalHr_On.Text
                .Range("G" & count_Row.ToString()).NumberFormat = "#,##0.00"
                .Range("G" & count_Row.ToString()).Value = txt_totalCost_on.Text

                .Range("L" & count_Row.ToString()).Value = "Total"
                .Range("M" & count_Row.ToString()).NumberFormat = "00.00"
                .Range("M" & count_Row.ToString()).Value = txt_totalHours_Group.Text
                .Range("G" & count_Row.ToString()).NumberFormat = "#,##0.00"
                .Range("N" & count_Row.ToString()).Value = txt_totalCost_group.Text

                count_Row = count_Row + 2
                .Range("J" & count_Row.ToString()).Value = "Summary"
                count_Row += 1
                .Range("J" & count_Row.ToString()).Value = "Training On the job :"
                .Range("K" & count_Row.ToString()).NumberFormat = "00.00"
                .Range("K" & count_Row.ToString()).Value = txt_sum_onthejob.Text
                .Range("L" & count_Row.ToString()).Value = "Hrs."
                count_Row += 1
                .Range("J" & count_Row.ToString()).Value = "Training Group :"
                .Range("K" & count_Row.ToString()).NumberFormat = "00.00"
                .Range("K" & count_Row.ToString()).Value = txt_traininggroup.Text
                .Range("L" & count_Row.ToString()).Value = "Hrs."
                count_Row += 1
                .Range("J" & count_Row.ToString()).Value = "Total :"
                .Range("K" & count_Row.ToString()).NumberFormat = "00.00"
                .Range("K" & count_Row.ToString()).Value = txt_total.Text
                .Range("L" & count_Row.ToString()).Value = "Hrs."

            Else
                For J = 9 To count_Row2
                    .Range("B" & J.ToString() & ":B" & J.ToString()).Borders(7).Weight = 2 ' xlThin
                    .Range("B" & J.ToString() & ":B" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("C" & J.ToString() & ":C" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("D" & J.ToString() & ":D" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("E" & J.ToString() & ":E" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("F" & J.ToString() & ":F" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("G" & J.ToString() & ":G" & J.ToString()).Borders(10).Weight = 2 ' xlThin


                    .Range("I" & J.ToString() & ":I" & J.ToString()).Borders(7).Weight = 2 ' xlThin
                    .Range("I" & J.ToString() & ":I" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("J" & J.ToString() & ":J" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("K" & J.ToString() & ":K" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("L" & J.ToString() & ":L" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("M" & J.ToString() & ":M" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                    .Range("N" & J.ToString() & ":N" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                Next

                count_Row2 = count_Row2 + 1
                .Range("B" & count_Row2.ToString() & ":G" & count_Row2.ToString()).Borders(8).Weight = 2 ' xlThin
                .Range("I" & count_Row2.ToString() & ":n" & count_Row2.ToString()).Borders(8).Weight = 2 ' xlThin
                count_Row = count_Row + 1
                .Range("E" & count_Row2.ToString()).Value = "Total"
                .Range("F" & count_Row2.ToString()).NumberFormat = "00.00"
                .Range("F" & count_Row2.ToString()).Value = txt_totalHr_On.Text
                .Range("G" & count_Row2.ToString()).NumberFormat = "#,##0.00"
                .Range("G" & count_Row2.ToString()).Value = txt_totalCost_on.Text

                .Range("L" & count_Row2.ToString()).Value = "Total"
                .Range("M" & count_Row2.ToString()).NumberFormat = "00.00"
                .Range("M" & count_Row2.ToString()).Value = txt_totalHours_Group.Text
                .Range("N" & count_Row2.ToString()).NumberFormat = "#,##0.00"
                .Range("N" & count_Row2.ToString()).Value = txt_totalCost_group.Text

                count_Row2 = count_Row2 + 2
                .Range("J" & count_Row2.ToString()).Value = "Summary"
                count_Row2 += 1
                .Range("J" & count_Row2.ToString()).Value = "Training on the job :"
                .Range("K" & count_Row2.ToString()).NumberFormat = "00.00"
                .Range("K" & count_Row2.ToString()).Value = txt_sum_onthejob.Text
                .Range("L" & count_Row2.ToString()).Value = "Hrs."
                count_Row2 += 1
                .Range("J" & count_Row2.ToString()).Value = "Training group :"
                .Range("K" & count_Row2.ToString()).NumberFormat = "00.00"
                .Range("K" & count_Row2.ToString()).Value = txt_traininggroup.Text
                .Range("L" & count_Row2.ToString()).Value = "Hrs."
                count_Row2 += 1
                .Range("J" & count_Row2.ToString()).Value = "Total :"
                .Range("K" & count_Row2.ToString()).NumberFormat = "00.00"
                .Range("K" & count_Row2.ToString()).Value = txt_total.Text
                .Range("L" & count_Row2.ToString()).Value = "Hrs."

            End If
        End With

        Try
            excelbooks.SaveAs(pathExcel.ToString + "\" + txt_empCode.Text + ".xlsx")
            excelapp.Quit()
            MsgBox("Report Complete")


        Catch ex As Exception
            MsgBox("NOT SAVED")
        End Try



    End Sub

    Public Sub addZero(ByVal hours1 As String, ByVal min1 As String, ByVal hours2 As String, ByVal min2 As String)
        If (Len(hours1) < 2) Then
            time_start = "0" + hours1
        Else
            time_start = hours1
        End If

        If (Len(min1) < 2) Then
            time_start = time_start + ".0" + min1
        Else
            time_start = time_start + "." + min1
        End If

        If (Len(hours2) < 2) Then
            time_end = "0" + hours2
        Else
            time_end = hours2
        End If
        If (Len(min2) < 2) Then
            time_end = time_end + ".0" + min2
        Else
            time_end = time_end + "." + min2
        End If
    End Sub

    Private Sub GroupBox2_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox2.Enter

    End Sub

    Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter

    End Sub

    Private Sub PictureBox1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles PictureBox1.DoubleClick

    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim MySql1 As MySqlConnection
        MySql1 = New MySqlConnection
        Dim object1 As Object

        MySql1.ConnectionString = "server=192.168.1.13;port = 3308;user id=" + "root" + ";password=" + "pisona" + ";database=testremote;Character Set =utf8"

        MySql1.Open()
        object1 = MsgBox("Are You Sure To Retired Personal", MsgBoxStyle.Information + MsgBoxStyle.YesNo, "Warning Message")
        If object1 = 6 Then
            Dim up_person_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
            Dim up_person_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
            Dim up_person_mysqlAdapter As New MySqlCommand

            Dim mySqlCommand As New MySqlCommand
            Dim mySqlAdaptor As New MySqlDataAdapter
            Dim mySqlReader As MySqlDataReader

            up_person_mysqlAdapter.CommandText = "Update  employee SET emp_status = '0' where emp_code = '" & txt_empCode.Text & "' "
            up_person_mysqlAdapter.Connection = MySql1
            up_person_mysqlAdapter.ExecuteNonQuery()

            MySql1.Close()

            MySql1.Open()

            mySqlCommand.CommandText = "DELETE  from training where emp_code ='" & txt_empCode.Text & "';"
            mySqlCommand.Connection = MySql1
            mySqlCommand.ExecuteNonQuery()
        Else

        End If


    End Sub

  
    Private Sub txt_totalCost_on_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_totalCost_on.TextChanged

    End Sub
    Private Sub txt_totalHr_On_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_totalHr_On.TextChanged

    End Sub
    Private Sub Label11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label11.Click

    End Sub

    Private Sub txt_totalCost_group_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_totalCost_group.TextChanged

    End Sub

    Private Sub txt_totalHours_Group_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_totalHours_Group.TextChanged

    End Sub

    Private Sub Label16_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label16.Click

    End Sub

    Private Sub txt_department_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_department.TextChanged

    End Sub
End Class